package com.sanqing.daoImpl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sanqing.bean.Employee;
import com.sanqing.dao.EmployeeDAO;
import com.sanqing.util.DBConnection;

public class EmployeeDAOImpl implements EmployeeDAO{

	/*
	 * (non-Javadoc)
	 * @see com.sanqing.dao.EmployeeDAO#addEmployee(com.sanqing.bean.Employee)
	 * 参数为Employee对象
	 * 该方法实现将employee对象中的数据存入数据库中
	 */
	@Override
	public void addEmployee(Employee employee) {
		// TODO Auto-generated method stub
		
		Connection conn=DBConnection.getConnection();
		String addSQL="insert into tb_employee(employeeID,employeeName,employeeSex,employeeBirth,employeePhone,employeePlace,joinTime,password,isLead) "
				+ "values (?,?,?,?,?,?,?,?,?)";
		
		PreparedStatement pstmt=null;
		
		
		try{
			pstmt=conn.prepareStatement(addSQL);
		
			pstmt.setInt(1,employee.getEmployeeID());
			pstmt.setString(2, employee.getEmployeeName());
			pstmt.setBoolean(3, employee.isEmployeeSex());
			pstmt.setDate(4, new Date(employee.getEmployeeBirth().getTime()));//解决sql中Date和util中Date不兼容
			pstmt.setString(5, employee.getEmployeePhone());
			pstmt.setString(6, employee.getEmployeePlace());
			pstmt.setDate(7,new Date(employee.getJoinTime().getTime()));
			pstmt.setString(8, employee.getPassword());
			pstmt.setBoolean(9, employee.isLead());
			pstmt.executeUpdate();
			
			}
		catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
		
	}
	
/*
 * (non-Javadoc)
 * @see com.sanqing.dao.EmployeeDAO#updateEmployee(com.sanqing.bean.Employee)
 * 此方法实现更新指定员工信息
 */
	@Override
	public void updateEmployee(Employee employee) {
		// TODO Auto-generated method stub
		Connection conn=DBConnection.getConnection();
		String updateSQL="update tb_employee set employeeName=?,employeeSex=?,employeeBirth=?,"
				+ "employeePhone=?,employeePlace=?,joinTime=?,password=?,isLead=? where employeeID=?";
		
		PreparedStatement pstmt=null;
		
		
		try{
			pstmt=conn.prepareStatement(updateSQL);
		
			pstmt.setString(1, employee.getEmployeeName());
			pstmt.setBoolean(2, employee.isEmployeeSex());
			pstmt.setDate(3, new Date(employee.getEmployeeBirth().getTime()));//解决sql中Date和util中Date不兼容
			pstmt.setString(4, employee.getEmployeePhone());
			pstmt.setString(5, employee.getEmployeePlace());
			pstmt.setDate(6,new Date(employee.getJoinTime().getTime()));
			pstmt.setString(7, employee.getPassword());
			pstmt.setBoolean(8, employee.isLead());
			pstmt.setInt(9,employee.getEmployeeID());
			
			pstmt.executeUpdate();
			
			}
		catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
		
		
	}

	/*
	 * (non-Javadoc)
	 * @see com.sanqing.dao.EmployeeDAO#deleteEmployee(com.sanqing.bean.Employee)
	 * 此方法实现删除指定员工
	 */
	@Override
	public void deleteEmployee(Employee employee) {
		// TODO Auto-generated method stub
		Connection conn=DBConnection.getConnection();
		String deleteSQL=" delete from tb_employee where employeeID=?";
		
		PreparedStatement pstmt=null;
		
		
		try{
			
			pstmt=conn.prepareStatement(deleteSQL);
			pstmt.setInt(1, employee.getEmployeeID());
			pstmt.executeUpdate();
			
			}
		catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
	}
/*
 * (non-Javadoc)
 * @see com.sanqing.dao.EmployeeDAO#findAllEmployee()
 * 此方法返回所有员工
 */
	@Override
	public List<Employee> findAllEmployee() {
		// TODO Auto-generated method stub
		Connection conn=DBConnection.getConnection();
		String findSQL="select * from tb_employee order by employeeID";
		
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		List<Employee> employees=new ArrayList<Employee>();
		
		try{
			pstmt=conn.prepareStatement(findSQL);
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				Employee employee=new Employee();
				employee.setEmployeeID(rs.getInt(1));
				employee.setEmployeeName(rs.getString(2));
				employee.setEmployeeSex(rs.getBoolean(3));
				employee.setEmployeeBirth(rs.getDate(4));
				employee.setEmployeePhone(rs.getString(5));
				employee.setEmployeePlace(rs.getString(6));
				employee.setJoinTime(rs.getDate(7));
				employee.setPassword(rs.getString(8));
				employee.setLead(rs.getBoolean(9));
				
				employees.add(employee);
			}
		}catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(rs);
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
		return employees;
	}

	/*
	 * (non-Javadoc)
	 * @see com.sanqing.dao.EmployeeDAO#findEmployeeById(int)
	 * 查找指定ID员工信息
	 */
	@Override
	public Employee findEmployeeById(int employeeID) {
		// TODO Auto-generated method stub
		Connection conn=DBConnection.getConnection();
		String findByIDSQL="select * from tb_employee where employeeID=?";
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		Employee employee=null;
		try{
			pstmt=conn.prepareStatement(findByIDSQL);
			pstmt.setInt(1, employeeID);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				employee=new Employee();
				employee.setEmployeeID(rs.getInt(1));
				employee.setEmployeeName(rs.getString(2));
				employee.setEmployeeSex(rs.getBoolean(3));
				employee.setEmployeeBirth(rs.getDate(4));
				employee.setEmployeePhone(rs.getString(5));
				employee.setEmployeePlace(rs.getString(6));
				employee.setJoinTime(rs.getDate(7));
				employee.setPassword(rs.getString(8));
				employee.setLead(rs.getBoolean(9));
			
			}
		}catch(SQLException e)
		{
			e.printStackTrace();
		}finally{
			DBConnection.close(rs);
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
		return employee;
	}
	
	

}
